var express = require('express');
var router = express.Router();
var request = require('request');

var CommonJS = require('../../../common/common');
var DBConfig = require('../../../db/dbconfig');
var Wx_config = require('../../../wx_config/wx_config');
var mysql = require('mysql');
var pool = mysql.createPool(DBConfig.mysql2);

/*
 * 订单状态
 * 客户：     order_status   shipping_status  status
 * 1.待付款    1              1                1
 * 2.已付款    2              -      
 * 3.已取消    3              -
 * 4.已完成    4              3
 * 5.待评价    5              3
 * 6.已评价    6              3
 * 7.已退款    7
 * 全部        -              -                1
 * 待付款      1              1
 * 配送中      2              2
 * 已完成      4              3
 * 待评价      5              3
 * 仓库：shipping_status
 * 1.待分配    2              1
 * 2.已分配    2              2
 * 3.已送达    2              3
 * 4.订单异常  2              4
 * 订单系统状态 status
 * 1. 有效
 * 2. 删除
 *   
 */


// 订单结算
router.post('/jkyx/order_confirm', function(req, res, next) {
	var param = req.body; 
	var account_id = param.account_id || ''; // 用户ID
	var goods = param.goods;
	var order_no = Wx_config.getWxPayOrdrID(); // 生成订单号
	var create_time = CommonJS.getNowFormatDate(); // 订单生成时间
	var add_time = CommonJS.timestamp();
	var	consignee = param.consignee || ''; // 收货人ID
	var	consignee_tel = param.consignee_tel || ''; // 收货人电话
	var	province_id = 13; // 省份ID  默认湖北
	var	city_id = 180; // 城市ID  默认武汉市
	var 	areas_id = param.areas_id || ''; // 区ID
	var	area_id = param.area_id || ''; // 街道ID
	var	address = param.address || ''; // 详细地址
	var	defray_type = param.defray_type; // 配送方式
	var	defray_id = param.defray_id; // 自提点ID
	var	freight_amount = param.freight_amount; // 运费
	var	amount = param.amount * 100; // 商品总金额
	var	pay_amount = param.pay_amount * 100; // 实际支付金额
	var	coupon_id = param.coupon_id || '0'; // 优惠券ID
	var	is_print_price = param.is_print_price || '1'; // 是否打印价格
	var	remark = param.remark || ''; // 特殊需求
	
	freight_amount = freight_amount * 100;
	
	pool.getConnection(function(err, connection) {
		if(err){
			res.end(JSON.stringify({
     			msg: '数据库连接失败',
     			status:'101',
     		}));
		}
		// 1.购物车中的商品状态修改为 2：已经提交订单
		// 2.订单表中增加商品
		var goods_len = goods.length;
		var cartSQL = 'update cart set status = 2 where id in';
		var cartIDs = '';
		var order_goods = '';
		var orderGoodsSQL = 'insert into order_goods (account_id,order_id,goods_id,spec_id,num,create_time) values ';
		for (var i = 0; i < goods_len; i ++) {
			cartIDs = cartIDs + goods[i].cart_id + ','
			var values = "'" + account_id + "'" + ',' 
	  	  			 + "'" + order_no + "'" + ',' 
	  	  			 + "'" +  goods[i].id + "'" + ',' 
	  	  			 + "'" +  goods[i].spec.id + "'" + ','
	  	  			 + "'" +  goods[i].num + "'" + ','
	  	  			 + "'" + create_time + "'";
		  	        if (i == (goods_len - 1)) {
		  	        		values = '(' + values + ')';
		  	        }else{
		  	        		values = '(' + values + '),';
		  	        }
		  	        order_goods = order_goods + values;
		}
		cartIDs = cartIDs.substring(0,(cartIDs.length - 1));
		cartSQL = cartSQL + ' (' + cartIDs + ');';
		orderGoodsSQL = orderGoodsSQL + order_goods + ';';
		var orderSQL = 'insert into orders (account_id,order_no,consignee,consignee_tel,province_id,city_id,areas_id,area_id,address,defray_type,defray_id,freight_amount,amount,pay_amount,coupon_id,is_print_price,remark,create_time,add_time) values '
										+ '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
		orderSQL = cartSQL + orderGoodsSQL + orderSQL;
		
		connection.query(orderSQL,[
				account_id,  
				order_no,
				consignee,
				consignee_tel,
				province_id,
				city_id,
				areas_id,
				area_id,
				address,
				defray_type,
				defray_id,
				freight_amount,
				amount,
				pay_amount,
				coupon_id,
				is_print_price,
				remark,
				create_time,
				add_time
			],function (err, results) {
			connection.release();
			if (err){
			  	res.end(JSON.stringify({
	     			msg: '数据库查询失败',
	     			status:'102',
	     			err: err
	     		}));
			}else{
				changeGoodsInfo(goods);
				var urlStr = 'http://localhost:8100/jkyx/conpou_used?account_id=' + account_id + '&coupon_id=' + coupon_id + '&type=0';
				request(urlStr, function (error, response, body) {})
				res.end(JSON.stringify({
	     			msg: '操作成功',
	     			status:'100',
	     			order_no: order_no
	     		}));	         
			}
		})
	})
})	

// 修改商品销量和库存
function changeGoodsInfo(goods){
	var len = goods.length;
	var specIds = '';
	for (var i = 0; i< len; i ++) {
		specIds = specIds + goods[i].spec.id + ',';
	}
	specIds = specIds.substring(0,specIds.length - 1);
	var ids = '(' + specIds + ')'; 
	var getSpecSql = 'select * from goods_spec where id in ' + ids;
	pool.getConnection(function(err, connection) {
		if(err){
			console.log('数据库连接失败1');
		}
		connection.query(getSpecSql,function (err, results) {
//			connection.release();
			if (err){
			  	console.log('数据库连接失败2');
			  	console.log(err);
			}else{
				var currentSpecs = results;
				var currentLen = currentSpecs.length;
				var caseSQL = '';
				var caseSQL2 = '';
				var ids = '';
				for (var i = 0; i < currentLen; i ++) {
					var currentSpec = results[i];
					caseSQL = caseSQL + ' when ' + currentSpec.id + ' then ' + (currentSpec.salecount + goods[i].num) + ' ';
					caseSQL2 = caseSQL2 + ' when ' + currentSpec.id + ' then ' + (currentSpec.stock - goods[i].num) + ' ';
				}
				var upadteSQL = 'UPDATE goods_spec set salecount = CASE id '
				                + caseSQL
				                + ' END '
				                + ' WHERE id in (' + specIds + ');';  
				var upadteSQL2 = 'UPDATE goods_spec set stock = CASE id '
				                + caseSQL2
				                + ' END '
				                + ' WHERE id in (' + specIds + ');';  
				 var updateSql = upadteSQL + upadteSQL2;
				 connection.query(updateSql,function (err, results) {
					connection.release();
					if (err){
					  	console.log('数据库连接失败3');
					  	console.log(err);
					}else{
						console.log('成功');
					}
				})
			}
		})
	})
}


// 订单列表
router.get('/jkyx/order_list', function(req, res, next) {
	var param = req.query || req.params; 
	var account_id = param.account_id || '';
	// 订单类型
	var order_type = param.order_type || '0';
	// 分页
	var page = param.page || 1;
    pool.getConnection(function(err, connection) {
		if(err){
			res.end(JSON.stringify({
     			msg: '数据库连接失败',
     			status:'101',
     		}));
		}
		switch (order_type){
			case '0':
				// 获取所有有效订单号
				connection.query(DBConfig.getOrderList,[account_id],function (err, results) {
					connection.release();
					if (err){
					  	res.end(JSON.stringify({
			     			msg: '数据库查询失败',
			     			status:'102',
			     			err: err
			     		}));
					}else{
						pool.getConnection(function(err, connection) {
							var allOrders = results;
							getOrderGoods(allOrders,connection,res,page);
						})
					}
				})
				break;
			case '1': // 待付款
				// 获取待付款订单号
				connection.query(DBConfig.getOrderListByType,[account_id,1,1],function (err, results) {
					connection.release();
					if (err){
					  	res.end(JSON.stringify({
			     			msg: '数据库查询失败',
			     			status:'102',
			     			err: err
			     		}));
					}else{
						pool.getConnection(function(err, connection) {
							var allOrders = results;
							getOrderGoods(allOrders,connection,res,page);
						})
					}
				})
				break;
			case '2': // 配送中
				// 获取已付款订单号  且订单已分配 == 配送中
				connection.query(DBConfig.getOrderListByType,[account_id,2,2],function (err, results) {
					connection.release();
					if (err){
					  	res.end(JSON.stringify({
			     			msg: '数据库查询失败',
			     			status:'102',
			     			err: err
			     		}));
					}else{
						pool.getConnection(function(err, connection) {
							var allOrders = results;
							getOrderGoods(allOrders,connection,res,page);
						})
					}
				})
				break;
			case '3': // 已完成
				// 获取已完成订单号
				connection.query(DBConfig.getOrderListByType,[account_id,4,3],function (err, results) {
					connection.release();
					if (err){
					  	res.end(JSON.stringify({
			     			msg: '数据库查询失败',
			     			status:'102',
			     			err: err
			     		}));
					}else{
						pool.getConnection(function(err, connection) {
							var allOrders = results;
							getOrderGoods(allOrders,connection,res,page);
						})
					}
				})
				break;
			case '4': // 待评价
				// 获取待评价订单号
				connection.query(DBConfig.getOrderListByType,[account_id,5,3],function (err, results) {
					connection.release();
					if (err){
					  	res.end(JSON.stringify({
			     			msg: '数据库查询失败',
			     			status:'102',
			     			err: err
			     		}));
					}else{
						pool.getConnection(function(err, connection) {
							var allOrders = results;
							getOrderGoods(allOrders,connection,res,page);
						})
					}
				})
				break;
			default:
				break;
		}
	})
});
// 订单详情
router.get('/jkyx/order_detail', function(req, res, next) {
	var param = req.query || req.params; 
	var account_id = param.account_id || '';
	// 订单ID
	var order_id = param.order_id || '';
	if (order_id == '') {
		res.end(JSON.stringify({
 			msg: '请传入订单ID',
 			status:'101',
 		}));
 		return;
	}
    pool.getConnection(function(err, connection) {
		if(err){
			res.end(JSON.stringify({
     			msg: '数据库连接失败',
     			status:'101',
     		}));
		}
		// 获取订单详情
		connection.query(DBConfig.getOrderDetail,[account_id,order_id,order_id,order_id,order_id,order_id,order_id],function (err, results) {
			connection.release();
			if (err){
			  	res.end(JSON.stringify({
	     			msg: '数据库查询失败',
	     			status:'102',
	     			err: err
	     		}));
			}else{
				pool.getConnection(function(err, connection) {
					getOrderGoods2(results,connection,res)
				})
			}
		})
	})
});

// 删除订单
router.get('/jkyx/order_delete', function(req, res, next) {
	var param = req.query || req.params; 
	var account_id = param.account_id || '';
	// 订单ID
	var order_id = param.order_id || '';
	if (order_id == '') {
		res.end(JSON.stringify({
 			msg: '请传入订单ID',
 			status:'101',
 		}));
 		return;
	}
    pool.getConnection(function(err, connection) {
		if(err){
			res.end(JSON.stringify({
     			msg: '数据库连接失败',
     			status:'101',
     		}));
		}
		// 获取订单详情
		connection.query(DBConfig.deleteOrderByID,[account_id,order_id],function (err, results) {
			connection.release();
			if (err){
			  	res.end(JSON.stringify({
	     			msg: '数据库查询失败',
	     			status:'102',
	     			err: err
	     		}));
			}else{
				res.end(JSON.stringify({
	     			msg: '操作成功',
	     			status:'100'
	     		}));
			}
		})
	})
});
// 取消订单
router.get('/jkyx/order_cancle', function(req, res, next) {
	var param = req.query || req.params; 
	var account_id = param.account_id || '';
	// 订单ID
	var order_no = param.order_no || '';
	// 优惠券ID
	var coupon_id = param.coupon_id || '';
	if (order_no == '') {
		res.end(JSON.stringify({
 			msg: '请传入订单号',
 			status:'101',
 		}));
 		return;
	}
	// 1.去微信关闭支付
	// 2.修改订单状态 order_status = 3
    pool.getConnection(function(err, connection) {
		if(err){
			res.end(JSON.stringify({
     			msg: '数据库连接失败',
     			status:'101',
     		}));
		}
		var urlStr = 'http://localhost:8100/jkyx/wxpay_closeOrder?out_trade_no=' + order_no;
		request(urlStr, function (error, response, body) {
			if (!error && response.statusCode == 200) {
				// 获取订单详情
				connection.query(DBConfig.cancelOrderByID,[account_id,order_no,coupon_id],function (err, results) {
					connection.release();
					if (err){
					  	res.end(JSON.stringify({
			     			msg: '数据库查询失败',
			     			status:'102',
			     			err: err
			     		}));
					}else{
						res.end(body);
					}
				})
			}
		})
	})
});

// 处理订单详情中的商品
function getOrderGoods2(results1,connection,res){
	var allOrders = results1[0];
	var order_nos = allOrders[0].order_no;
	// 订单地址
	var order_address = {
		provice_name: results1[1][0].region_name, // 省份
		city_name: results1[2][0].region_name,  // 市
		areas_name: results1[3][0].region_name, // 区
		area_name: results1[4][0].region_name,  // 街道
		address: results1[0][0].address // 详细地址	
	}
	// 收货人
	var order_consignee = {
		consignee: results1[0][0].consignee, // 收货人姓名
		consignee_tel: results1[0][0].consignee_tel // 收货人手机号
	}
	if (results1[5].length == 0) { // 还没分配送货人
		var order_shiper = {
			name: '', // 配送员姓名
			telno: '', // 配送员手机号
		}
	}else{
		var order_shiper = {
			name: results1[5][0].name, // 配送员姓名
			telno: results1[5][0].telno, // 配送员手机号
		}
	}	
	var sql1 = 'select * from order_goods where order_id =' + order_nos;
	// 获取订单中的商品 商品ID  规格ID  商品数量
	connection.query(sql1,function (err, results) {
		if (err){
		  	res.end(JSON.stringify({
     			msg: '数据库查询失败',
     			status:'102',
     			err: err
     		}));
		}else{
			var retGoods = results;
			// 处理商品
			var goodsIDS = ''; // 商品IDs
			var specIDS = ''; // 规格
			var retGoods_Len = retGoods.length;
			for (var j = 0; j < retGoods_Len; j ++) {
				goodsIDS = goodsIDS + retGoods[j].goods_id + ',';
				specIDS = specIDS + retGoods[j].spec_id + ',';
			} 
			// 去掉最后一个','
			goodsIDS = goodsIDS.substring(0,(goodsIDS.length - 1));
			specIDS = specIDS.substring(0,(specIDS.length - 1));
			var selGoods = 'select * from goods where id in (' + goodsIDS + '); select * from goods_spec where id in (' + specIDS + ');';
			connection.query(selGoods,function (err, results) {
				connection.release();
				if (err){
				  	res.end(JSON.stringify({
		     			msg: '数据库查询失败',
		     			status:'103',
		     			err: err
		     		}));
				}else{
					// 处理商品
					var cartGoods = results[0];
					var cartGoods_len = cartGoods.length;
					// 加规格
					var cartSpecs = results[1];
					var cartSpecs_len = cartSpecs.length;
					var retOreders = [];
					var order_item = {};
					var tempGoods = [];
					var order_num = 0;
					// 商品循环遍历 
					for (var i = 0; i < retGoods_Len; i ++) {
						var good = getGoods(cartGoods,retGoods[i].goods_id);
						var spec = getSpecs(cartSpecs,retGoods[i].spec_id);
						var num = retGoods[i].num;
						order_num = order_num + num;
						var item = {
							id: good.id, // 商品ID
							name: good.name, // 商品名字
							descr: good.descr, // 商品描述
							imgs: good.imgs, // 商品轮播图
							detail_imgs: good.detail_imgs, // 商品详细
							spec: {
								id: spec.id,  // 规格ID
								price: spec.price, // 价格
								origprice: spec.origprice, // 原价
								spec: spec.spec, // 规格
								salecount: spec.salecount,  // 销量
								stock: spec.stock, // 库存
							},
							num: num,  // 商品数量
						}
						tempGoods.push(item);
					}
					order_item = {
						id: allOrders[0].id,
						order_no: allOrders[0].order_no,
						payment_id: allOrders[0].payment_id,
						freight_amount: allOrders[0].freight_amount,
						amount: allOrders[0].amount,
						pay_amount: allOrders[0].pay_amount,
						coupon_id: allOrders[0].coupon_id,
						recharge_pay_amount: allOrders[0].recharge_pay_amount,
						pay_amount_other: allOrders[0].pay_amount_other,
						is_print_price: allOrders[0].is_print_price,
						defray_type: allOrders[0].defray_type,
						order_status: allOrders[0].order_status,
						shipping_status: allOrders[0].shipping_status,
						pay_time: allOrders[0].pay_timer,
						create_time: allOrders[0].create_time,
						num: order_num,
						goods: tempGoods,
					}
					retOreders = order_item;
					res.end(JSON.stringify({
		     			msg: '操作成功',
		     			status:'100',
		     			order_detail: retOreders,
		     			order_address: order_address,
		     			order_consignee: order_consignee,
		     			order_shiper: order_shiper
		     		}));	  
				}
			})
		}
	})
}

// 处理订单列表中的商品
function getOrderGoods(allOrders,connection,res,page){
	var order_nos = '';
	var allOrders_Len = allOrders.length;
	if (allOrders_Len == 0) {
		res.end(JSON.stringify({
 			msg: '操作成功',
 			status:'100',
 			orders: []
 		}));
 		return;
	}
	for (var j = 0; j < allOrders_Len; j ++) {
		order_nos = order_nos + allOrders[j].order_no + ',';
	} 
	// 去掉最后一个','
	order_nos = order_nos.substring(0,(order_nos.length - 1));
	var sql1 = 'select * from order_goods where order_id in (' + order_nos + ');';
	// 获取订单中的商品 商品ID  规格ID  商品数量
	connection.query(sql1,function (err, results) {
		if (err){
		  	res.end(JSON.stringify({
     			msg: '数据库查询失败',
     			status:'102',
     			err: err
     		}));
		}else{
			var retGoods = results;
			// 处理商品
			var goodsIDS = ''; // 商品IDs
			var specIDS = ''; // 规格
			var retGoods_Len = retGoods.length;
			for (var j = 0; j < retGoods_Len; j ++) {
				goodsIDS = goodsIDS + retGoods[j].goods_id + ',';
				specIDS = specIDS + retGoods[j].spec_id + ',';
			} 
			// 去掉最后一个','
			goodsIDS = goodsIDS.substring(0,(goodsIDS.length - 1));
			specIDS = specIDS.substring(0,(specIDS.length - 1));
			var selGoods = 'select * from goods where id in (' + goodsIDS + '); select * from goods_spec where id in (' + specIDS + ');';
			connection.query(selGoods,function (err, results) {
				connection.release();
				if (err){
				  	res.end(JSON.stringify({
		     			msg: '数据库查询失败',
		     			status:'103',
		     			err: err
		     		}));
				}else{
					// 处理商品
					var cartGoods = results[0];
					var cartGoods_len = cartGoods.length;
					// 加规格
					var cartSpecs = results[1];
					var cartSpecs_len = cartSpecs.length;
					var retOreders = [];
					// 订单循环遍历
					for (var j = 0; j < allOrders_Len; j ++) {
						var order_item = {};
						var tempGoods = [];
						var order_num = 0;
						// 商品循环遍历 
						for (var i = 0; i < retGoods_Len; i ++) {
							if (allOrders[j].order_no == retGoods[i].order_id) {
								var good = getGoods(cartGoods,retGoods[i].goods_id);
								var spec = getSpecs(cartSpecs,retGoods[i].spec_id);
								var num = retGoods[i].num;
								order_num = order_num + num;
								var item = {
									id: good.id, // 商品ID
									name: good.name, // 商品名字
									descr: good.descr, // 商品描述
									imgs: good.imgs, // 商品轮播图
									detail_imgs: good.detail_imgs, // 商品详细
									spec: {
										id: spec.id,  // 规格ID
										price: spec.price, // 价格
										origprice: spec.origprice, // 原价
										spec: spec.spec, // 规格
										salecount: spec.salecount,  // 销量
										stock: spec.stock, // 库存
									},
									num: num,  // 商品数量
								}
								tempGoods.push(item);
							}
						}
						order_item = {
							id: allOrders[j].id,
							order_no: allOrders[j].order_no,
							consignee: allOrders[j].consignee,
							consignee_tel: allOrders[j].consignee_tel,
							coupon_id: allOrders[j].coupon_id,
							payment_id: allOrders[j].payment_id,
							freight_amount: allOrders[j].freight_amount,
							amount: allOrders[j].amount,
							pay_amount: allOrders[j].pay_amount,
							recharge_pay_amount: allOrders[j].recharge_pay_amount,
							pay_amount_other: allOrders[j].pay_amount_other,
							is_print_price: allOrders[j].is_print_price,
							defray_type: allOrders[j].defray_type,
							order_status: allOrders[j].order_status,
							shipping_status: allOrders[j].shipping_status,
							create_time: allOrders[j].create_time.split(' ')[0],
							num: order_num,
							goods: tempGoods,
						}
						retOreders.push(order_item);
					} 
					// 分页 每页 10 个 
					var len = retOreders.length;
					var count = 2;
					var allPage = parseInt(len)/count;  // 总页数
					var pageStr = allPage.toString();
					// 不能被整除
					if (pageStr.indexOf('.')>0) {
						allPage = parseInt(pageStr.split('.')[0]) + 1; 
					}
					var start = (page - 1) * count;
					var end = start + count;
					var jsonArray = retOreders.slice(0,end);
					res.end(JSON.stringify({
		     			msg: '操作成功',
		     			status:'100',
		     			allPage: allPage,
		     			page: page,
		     			orders: jsonArray,
		     		}));	  
				}
			})
		}
	})
}

// 根据商品 ID  寻找商品数组中的商品
// goods: 商品数组
// goods_id: 商品ID
function getGoods(goods,goods_id){
	var len = goods.length;
	for (var i = 0; i < len; i ++) {
		if (goods[i].id == goods_id) {
			if (typeof goods[i].imgs === 'string') {
				goods[i].imgs = JSON.parse(goods[i].imgs);
			}
			return goods[i];
		}
	}
	return false;
}
// 根据规格ID 寻找规格数组中的规格
// specs: 规格数组
// spec_id: 规格ID
function getSpecs(specs,spec_id){
	var len = specs.length;
	for (var i = 0; i < len; i ++) {
		if (specs[i].id == spec_id) {
			return specs[i];
		}
	}
	return false;
}

module.exports = router;
